返回首页

Oracle 数据库性能排查 SQL

Oracle 性能排查与锁表处理常用 SQL

一、查看会话内存占用

查看哪个会话在占用物理内存(PGA)

Select Server, Osuser, Name, Value / 1024 / 1024 Mb, s.Sql_Id, Spid, s.*
From V$session s, V$sesstat St, V$statname Sn, V$process p
Where St.Sid = s.Sid
And St.Statistic# = Sn.Statistic#
And Sn.Name Like 'session pga memory'
And p.Addr = s.Paddr
Order By Value Desc;

二、查询最占用资源的 SQL

查询高磁盘读取的 SQL(消耗资源TOP)

select b.username username,
       a.disk_reads reads,
       a.executions exec ,
       a.disk_reads / decode(a.executions, 0, 1, a.executions) rds_exec_ratio,
       a.sql_text Statement
  from v$sqlarea a, dba_users b
 where a.parsing_user_id = b.user_id
   and a.disk_reads > 100000
 order by a.disk_reads desc;

使用频率最高的 5 条 SQL

select sql_text, executions
 from (select sql_text,
              executions,
              rank() over(order by executions desc) exec_rank
         from v$sql)
where exec_rank <= 5;

磁盘读取最多 TOP5 SQL

select disk_reads, sql_text
 from (select sql_text,
              disk_reads,
              dense_rank() over(order by disk_reads desc) disk_reads_rank
         from v$sql)
where disk_reads_rank <= 5;

逻辑读(内存消耗)最多 SQL

select buffer_gets, sql_text
 from (select sql_text,
              buffer_gets,
              dense_rank() over(order by buffer_gets desc) buffer_gets_rank
         from v$sql)
where buffer_gets_rank <= 5;

三、查看当前活动会话

SELECT status, a.sql_exec_start AS "开始时间", SYSDATE AS "当前时间",
       round((SYSDATE - a.sql_exec_start) * 24 * 60) AS "执行时长min", b.sql_id,
       b.sql_text, b.sql_fulltext, a.sid, a.serial#, a.blocking_session,
       a.username, a.machine, a.event,
       'alter system kill session ''' || a.sid || ',' || a.serial# || ''';' AS killstment
FROM v$session a, v$sqlarea b
WHERE a.sql_id = b.sql_id
AND status = 'ACTIVE'
ORDER BY 4 DESC;